# -*- coding: utf-8 -*-
'''
Created on Aug 4, 2012

@author: LONG HOANG GIANG
'''
import sqlite3 as db
import re

conn2 = db.connect('truyencotich')
c2 = conn2.cursor()
c2.execute("DROP TABLE IF EXISTS tblChapters")
c2.execute("DROP TABLE IF EXISTS tblDetail")
c2.execute("CREATE TABLE tblChapters(`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT)")
c2.execute("CREATE INDEX chapter_idx ON tblChapters(id)")
c2.execute("CREATE TABLE tblDetail(`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `chapterId` INTEGER, `detail` TEXT)")
c2.execute("CREATE INDEX detail_idx ON tblDetail(chapterId)")

conn1 = db.connect('Content')
c1 = conn1.cursor()

c1.execute("SELECT * FROM items WHERE parentId>0 AND isLeaf=0")
a = c1.fetchall()

for item in a:
    itemId = item[1]
    c1.execute("SELECT * FROM items WHERE parentId={0}".format(itemId))
    b = c1.fetchone()
    detail = b[2]
    detail = re.sub(r"\n", "<br />", detail)
    detail = re.sub(r"<br />{2,}", "<br />", detail)
    detail = '''<h4>{0}</h4><br /><br />{1}'''.format(item[2], detail)
    print item[2]
    print detail
    print "\n\n\n"
    
    c2.execute('''INSERT INTO tblChapters(title) VALUES('{0}')'''.format(item[2]))
    chapterId = c2.lastrowid
    c2.execute('''INSERT INTO tblDetail(chapterId, detail) VALUES({0}, '{1}')'''.format(chapterId, detail))

c1.close()

conn1.close()

c2.close()
conn2.commit()
conn2.close()

print '>finished'
import os
os._exit(1)

